package com.server.database;

import org.springframework.util.StringUtils;

public class SQLCreator {

	/**
	 * SQL 数量合计语句生成
	 * @param tableName
	 * @param whereStr
	 * @param countFields：要查询的字段，只能是一个，默认为“*”
	 * @param needDistinct：是否需要Distinct查询字段
	 * @return
	 * @author ServerZhang
	 * @date 2015年9月23日
	 */
	public static String getSqlCount_SQLserver(String tableName,String whereStr,
			String countFields, Boolean needDistinct){
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select count(");
		if (!StringUtils.isEmpty(countFields)) {
			if (needDistinct != null && needDistinct == true) {
				sqlStr.append("DISTINCT ");
			}
			sqlStr.append(countFields);
		}else {
			sqlStr.append("*");
		}
		sqlStr.append(") from ");
		sqlStr.append(tableName);
		if(!StringUtils.isEmpty(whereStr)){
			sqlStr.append(" where ").append(whereStr);
		}
		return sqlStr.toString();
	}
	
	/**
	 * SQL Server 查询语句生成
	 * @param pageIndex
	 * @param pageSize
	 * @param tableName
	 * @param whereStr
	 * @param sortStr
	 * @param fields：要查询的字段，用逗号隔开，默认为“*”
	 * @param needDistinct：是否需要Distinct查询字段
	 * @return
	 * @author ServerZhang
	 * @date 2015年9月23日
	 */
	public static String getSql_SQLServer(int pageIndex,int pageSize,
			String tableName,String whereStr,String sortStr,
			String fields, Boolean needDistinct){
		Integer firstIndex = pageIndex*pageSize; 
		Integer lastIndex = pageSize*(pageIndex+1);
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" WITH query AS (SELECT inner_query.*,ROW_NUMBER() OVER (ORDER BY  CURRENT_TIMESTAMP) as ROWNO  FROM( select ");
		if (needDistinct != null && needDistinct == true) {
			sqlStr.append("DISTINCT ");
		}
		sqlStr.append("TOP ").append(lastIndex);
		if (!StringUtils.isEmpty(fields)) {
			sqlStr.append(" ").append(fields).append(" ");
		}else {
			sqlStr.append(" * ");
		}
		sqlStr.append("from ").append(tableName);
		if(!StringUtils.isEmpty(whereStr)){
			sqlStr.append(" where ").append(whereStr);
		}
		if(!StringUtils.isEmpty(sortStr)){
			sqlStr.append(" order by ").append(sortStr);
		}
		sqlStr.append(" ) inner_query ) SELECT * FROM query WHERE ROWNO >=").append(firstIndex+1)
		.append(" AND ROWNO < ").append(lastIndex==Integer.MAX_VALUE?Integer.MAX_VALUE:lastIndex+1);
		return sqlStr.toString();
	}
	
	/**
	 * Oracle 查询语句生成
	 * @param pageIndex
	 * @param pageSize
	 * @param tableName
	 * @param whereStr
	 * @param sortStr
	 * @param fields
	 * @param needDistinct
	 * @return
	 * @author ServerZhang
	 * @date 2016年5月12日
	 */
	public static String getSql_Oracle(int pageIndex,int pageSize,
			String tableName,String whereStr,String sortStr,
			String fields, Boolean needDistinct){
		Integer firstIndex = pageIndex*pageSize; 
		Integer lastIndex = pageSize*(pageIndex+1);
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" WITH QUERY AS (SELECT inner_query.*,ROWNUM AS row_num  FROM( SELECT ");
		if (needDistinct != null && needDistinct == true) {
			sqlStr.append("DISTINCT ");
		}
		if (!StringUtils.isEmpty(fields)) {
			sqlStr.append(" ").append(fields).append(" ");
		}else {
			sqlStr.append(" * ");
		}
		sqlStr.append("FROM ").append(tableName);
		if(!StringUtils.isEmpty(whereStr)){
			sqlStr.append(" WHERE ").append(whereStr);
		}
		if(!StringUtils.isEmpty(sortStr)){
			sqlStr.append(" ORDER BY ").append(sortStr);
		}
		sqlStr.append(" ) inner_query ) SELECT * FROM QUERY WHERE row_num BETWEEN ").append(firstIndex+1)
		.append(" AND ").append(lastIndex==Integer.MAX_VALUE?Integer.MAX_VALUE:lastIndex+1);
		return sqlStr.toString();
	}
}
